Database Changes for 4.33

This section contains details of database changes between the 4.32 and 4.33 releases.

You can also download the 4.33 data dictionary (which contains all database tables and entity relationship diagrams) on the Communities website here.

Table changes:

New tables included in this release

Table Name	Column Name
HOURS_STATISTICS	A table to store CDR Hours Statistics Field.

New columns added to existing tables

Table Name	Column Name	Type (Size)	Nullable	Description
GROUP_DEFS	GROUP_APP_ID	VARCHAR2(40)	Y	Optional application category for this group. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=GROUP_APP_ID)]
LEARNER_AIMS	DEVOLVED_AREA_MONITORING_5	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
LEARNER_AIMS	DEVOLVED_AREA_MONITORING_6	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
LEARNER_AIMS	OFF_THE_JOB_ACT_TRAINING_HOURS	NUMBER(4)	Y	Collects the total Actual off-the-job training hours.
PEOPLE_ADDITIONAL_DETAILS	DECLARED_SEX	VARCHAR2(40)	Y	Declared (not legal) Sex associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=DECLARED_SEX)]
PEOPLE_ADDITIONAL_DETAILS	GENDER	VARCHAR2(40)	Y	Gender associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=GENDER)]
PEOPLE_ADDITIONAL_DETAILS	PRONOUNS	VARCHAR2(40)	Y	Pronouns associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=PRONOUNS)]
PEOPLE_UNITS_CDR	ES_FUND_FLAG	VARCHAR2(1)	Y	Indicates whether the Essential Skills enrolment should be funded by FLU.
PEOPLE_UNITS_CDR	PROG_ACHIEVEMENT	VARCHAR2(2)	Y	The overall achievement for the program [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_ACHIEVEMENT)].
PEOPLE_UNITS_CDR	PROG_FAST_TRACK	VARCHAR2(1)	Y	Indicates whether the enrolment is on fast track for the program [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_FAST_TRACK)].
PEOPLE_UNITS_CDR	RETURN_IND	VARCHAR2(1)	Y	Indicates whether the record is to be included in any data returns [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=RETURN_IND)].
PEOPLE_UNITS_CDR	TAKE_EXAM_YN	VARCHAR2(1)	Y	Indicates whether the student intends to take the exam at the end of the enrolment or not.
PEOPLE_UNITS_SPECIAL	DEVOLVED_AREA_MONITORING_5	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
PEOPLE_UNITS_SPECIAL	DEVOLVED_AREA_MONITORING_6	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
PEOPLE_UNITS_SPECIAL	OFF_THE_JOB_ACT_TRAINING_HOURS	NUMBER(4)	Y	Collects the total Actual off-the-job training hours.
UIO_CDR	ES_FUND_FLAG	VARCHAR2(1)	Y	Indicates whether the Essential Skills enrolment should be funded by FLU.
UIO_CDR	MAIN_CAMPUS	VARCHAR2(10)	Y	The location code for each campus within the six colleges [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=MAIN_CAMPUS)].
UIO_CDR	NOTIONAL_HOURS	NUMBER(5,2)	Y	The required non-classroom hours for the course i.e. student out on placement or tutorials.
UIO_CDR	PROG_CODE	VARCHAR2(10)	Y	The code for the program of study, including traineeship [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_CODE)].
UIO_CDR	PROG_YEAR	VARCHAR2(1)	Y	The program year of the enrolment [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_YEAR)].
UIO_CDR	TAKE_EXAM_YN	VARCHAR2(1)	Y	Indicates whether the student intends to take the exam at the end of the enrolment or not.
UIO_CDR	TENHOUR_IND	VARCHAR2(1)	Y	Indicates whether the course is longer than 10 hours [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=TENHOUR_IND)].
UIO_QUAL_AIMS	DEVOLVED_AREA_MONITORING_5	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
UIO_QUAL_AIMS	DEVOLVED_AREA_MONITORING_6	VARCHAR2(3)	Y	Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]

View changes:

New views included in this release

New columns in existing views

View Name	Column Name
DM_ENQ_LEARNER_ENQUIRIES	PROGRESS_CODE
DM_ENQ_LEARNER_ENQUIRIES	PROGRESS_CODE_DESCRIPTION
DM_ENQ_LEARNER_ENQUIRIES	PROGRESS_DATE_D
DM_ENQ_LEARNER_ENQUIRIES	PROGRESS_STATUS
DM_ENQ_LEARNER_ENQUIRIES	PROGRESS_STATUS_DESCRIPTION
EBS_BULK_ENROLMENT	SLDD
EBS_BULK_ENROLMENT	TOTAL_S_HOURS
EBS_BULK_ENROLMENT	DEVOLVED_AREA_MONITORING_6
EBS_BULK_ENROLMENT	RETURN_IND
EBS_BULK_ENROLMENT	OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_BULK_ENROLMENT	ESER_YN
EBS_BULK_ENROLMENT	AWARD_DATE
EBS_BULK_ENROLMENT	DEVOLVED_AREA_MONITORING_5
EBS_CDRGENERATERETURN	PC3
EBS_CDRGENERATERETURN	NIMDM2017_QUINTILES
EBS_CDRGENERATERETURN	MDM_RANK2017
EBS_CDRGENERATERETURN	MAIN_CAMPUS
EBS_CDRGENERATERETURN	MAINSTREAM_YN
EBS_CDRGENERATERETURN	DEP_NO_CARE
EBS_CDRGENERATERETURN	ALEVEL_GCSE
EBS_CDRGENERATERETURN	PRIORITY_AREA
EBS_CDRGENERATERETURN	VPRS
EBS_CDRGENERATERETURN	TENHOUR_IND
EBS_CDRGENERATERETURN	STUDENT_EMAIL
EBS_CDRGENERATERETURN	STUDENT_DISAB
EBS_CDRGENERATERETURN	PROG_YEAR
EBS_CDRGENERATERETURN	PROG_OF_STUDY
EBS_CDRGENERATERETURN	PROG_FAST_TRACK
EBS_CDRGENERATERETURN	PROG_CODE
EBS_CDRGENERATERETURN	PROG_ACHIEVEMENT
EBS_ENROLMENT_PROG_AIMS	DEVOLVED_AREA_MONITORING_6
EBS_ENROLMENT_PROG_AIMS	OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_ENROLMENT_PROG_AIMS	DEVOLVED_AREA_MONITORING_5
EBS_EXAM_CANDIDATES	PRONOUNS
EBS_EXAM_CANDIDATES	DECLARED_SEX
EBS_EXAM_CANDIDATES	GENDER
EBS_ILR_GENERATION_SOURCE	DEVOLVED_AREA_MONITORING_5
EBS_ILR_GENERATION_SOURCE	OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_ILR_GENERATION_SOURCE	DEVOLVED_AREA_MONITORING_6
EBS_LEARNERS	DECLARED_SEX
EBS_LEARNERS	GENDER
EBS_LEARNERS	PRONOUNS
EBS_LEARNERS_DEDUP	DECLARED_SEX
EBS_LEARNERS_DEDUP	GENDER
EBS_LEARNERS_DEDUP	PRONOUNS
EBS_LEARNERS_LLWR	PRONOUNS
EBS_LEARNERS_LLWR	DECLARED_SEX
EBS_LEARNERS_LLWR	GENDER
EBS_LEARNER_AIMS	AREA
EBS_LEARNER_AIMS	OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_LEARNER_AIMS	DEVOLVED_AREA_MONITORING_6
EBS_LEARNER_AIMS	DEVOLVED_AREA_MONITORING_5
EBS_LEARNER_ENROLMENTS	RETURN_IND
EBS_LEARNER_ENROLMENTS	START_TIME
EBS_LEARNER_ENROLMENTS	STUDENT_HOURS
EBS_LEARNER_ENROLMENTS	TAKE_EXAM_YN
EBS_LEARNER_ENROLMENTS	PROG_FAST_TRACK
EBS_LEARNER_ENROLMENTS	PROG_ACHIEVEMENT
EBS_LEARNER_ENROLMENTS	NOTIONAL_STUDENT_HOURS
EBS_LEARNER_ENROLMENTS	LONG_DESCRIPTION
EBS_LEARNER_ENROLMENTS	TOTAL_STUDENT_HOURS
EBS_LEARNER_ENROLMENTS	END_TIME
EBS_LEARNER_ENROLMENTS	COURSE_OCCURRENCE_CODE
EBS_LEARNER_ENROLMENTS	ES_FUND_FLAG
EBS_SCHEMA_ENTITIES	ROLE
EBS_SCHEMA_ENTITIES	ISACTIVE
EBS_SCHEMA_ENTITIES	ROLE_DEFS_ID
EBS_SDR_STUDENTS_BASE_2020	RECORD_TYPE
EBS_UIO	COURSE_HOURS
EBS_UIO	ES_FUND_FLAG
EBS_UIO	MAIN_CAMPUS
EBS_UIO	NOTIONAL_HOURS
EBS_UIO	PROG_CODE
EBS_UIO	TENHOUR_IND
EBS_UIO	PROG_YEAR
EBS_UIO	TAKE_EXAM_YN
EBS_UIO_LEARNING_AIMS	DEVOLVED_AREA_MONITORING_5
EBS_UIO_LEARNING_AIMS	DEVOLVED_AREA_MONITORING_6
EBS_UIO_LLWR	COURSE_HOURS
EBS_UIO_LLWR	TENHOUR_IND
EBS_UIO_LLWR	TAKE_EXAM_YN
EBS_UIO_LLWR	ES_FUND_FLAG
EBS_UIO_LLWR	MAIN_CAMPUS
EBS_UIO_LLWR	NOTIONAL_HOURS
EBS_UIO_LLWR	PROG_CODE
EBS_UIO_LLWR	PROG_YEAR
EBS_UNIT_SPECIAL	DEVOLVED_AREA_MONITORING_5
EBS_UNIT_SPECIAL	DEVOLVED_AREA_MONITORING_6
EBS_UNIT_SPECIAL	OFF_THE_JOB_ACT_TRAINING_HOURS

Columns dropped from existing views

View Name	Column Name
EBS_CDRGENERATERETURN	DISABILITY

Service Pack 1

New column added to an existing table

Table Name	Column Name	Type (Size)	Nullable	Description
PEOPLE_HESA	NATION	Varchar2(2)	Y	Specifically used for the HESA SA return. This field defines the country of a learner’s legal nationality. LOV selection is drawn from the HESA_Verifiers table where RV_Domain = ‘NATION’.

Central and Shape

Learn about the features of Central and Shape, such as applications, learner, curriculum, and so on.

Ontrack Staff Hub

Learn about the the features of Ontrack Staff Hub, such as concierge, safeguarding, and so on.

Ontrack Learner Hub

Learn about the features of Ontrack Learner Hub, such as applications, enrolments, and so on.

Ontrack Contact Hub

Learn about the features of Ontrack Contact Hub, such as attendance, progress, and so on.

Tribal Communities

Learn about the Tribal products, knowledge bases, raising a support ticket, and much more.

Go to Tribal Communities